﻿Public Class bubsinessCliente
    'Funcion para traer clientes que realizaron compras en el ultimo mes
    Public Function TraeClientesAdjudicados() As List(Of clsCliente)
        Dim Cn As New SqlClient.SqlConnection
        Dim Cm As New SqlClient.SqlCommand
        Dim Dr As SqlClient.SqlDataReader
        Dim ListaClientes As New List(Of clsCliente)

        Dim Conf As New clsConfiguraciones()
        Dim dao_Conf As New daoConfiguraciones()
        Conf = dao_Conf.TraeConfiguraciones()

        Try
            Cn.ConnectionString = My.Resources.Conexion
            Cn.Open()
            Cm.CommandType = CommandType.StoredProcedure
            Cm.CommandText = "SP_TRAER_CLIENTES_ADJUDICADOS"

            Cm.Parameters.Add("@fecha_desde", SqlDbType.Int)
            Cm.Parameters("@fecha_desde").Value = Date.Now().AddMonths(-1)

            Cm.Parameters.Add("@Cod_Suc", SqlDbType.Int)
            Cm.Parameters("@Cod_Suc").Value = Conf.Cod_Suc

            Cm.Connection = Cn
            Dr = Cm.ExecuteReader
            If Dr.HasRows = True Then
                While Dr.Read
                    Dim Cliente As New clsCliente()
                    Cliente.Cli_Id = Dr.GetInt32(0)
                    Cliente.Nombre = Dr.GetString(1).Trim.ToUpper
                    Cliente.Apellido = Dr.GetString(2).Trim.ToUpper

                    If Dr.IsDBNull(3) Then
                        Cliente.Direccion = "-"
                    Else
                        Cliente.Direccion = Dr.GetString(3).Trim.ToUpper
                    End If
                    If Dr.IsDBNull(4) Then
                        Cliente.Altura = "-"
                    Else
                        Cliente.Altura = Dr.GetString(4).Trim.ToUpper
                    End If
                    If Dr.IsDBNull(5) Then
                        Cliente.Depto = "-"
                    Else
                        Cliente.Depto = Dr.GetString(5).Trim.ToUpper
                    End If
                    If Dr.IsDBNull(6) Then
                        Cliente.Dni = "-"
                    Else
                        Cliente.Dni = Dr.GetString(6).Trim.ToUpper
                    End If
                    If Dr.IsDBNull(7) Then
                        Cliente.Tel = "-"
                    Else
                        Cliente.Tel = Dr.GetString(7).Trim.ToUpper
                    End If
                    If Dr.IsDBNull(8) Then
                        Cliente.Cuit = "-"
                    Else
                        Cliente.Cuit = Dr.GetString(8).Trim.ToUpper
                    End If
                    If Dr.IsDBNull(9) Then
                        Cliente.Email = "-"
                    Else
                        Cliente.Email = Dr.GetString(9).Trim.ToUpper
                    End If

                    Cliente.Tipo = Dr.GetString(10).Trim.ToUpper
                    Cliente.Cod_Suc = Dr.GetInt32(11)

                    ListaClientes.Add(Cliente)
                    Cliente.Dispose()
                End While

                Dr.Close()
                Cn.Close()
            End If
            Return ListaClientes
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            Conf.Dispose()
            Cn.Dispose()
            Cm.Dispose()
        End Try
    End Function


    ''Funcion para el informe de clientes
    Public Function ConsultaCliente(ByVal Cli_Id As Int32, ByVal DNI As String, ByVal CUIT As String, ByVal Nombre As String, ByVal Apellido As String, ByVal Direccion As String, ByVal DirNro As String, ByVal DirDepto As String, ByVal Telefono As String, ByVal Email As String, ByVal Tipo As String, ByVal Cod_Suc As Int32) As List(Of clsCliente)
        Dim ListaClientes As New List(Of clsCliente)
        Dim ClienteDao As New daoCliente()


        Try
            ''Si todo està bacio busco todos los articulos
            If Cli_Id = 0 And DNI.Trim.Length = 0 And CUIT.Trim.Length = 0 And Nombre.Trim.Length = 0 And Apellido.Trim.Length = 0 And Direccion.Trim.Length = 0 And DirNro.Trim.Length = 0 And DirDepto.Trim.Length = 0 And Telefono.Trim.Length = 0 And Email.Trim.Length = 0 Then

                ListaClientes = ClienteDao.TraeArticulosPorSucursalTipo(Cod_Suc, Tipo)
                Return ListaClientes
            End If

            ''Primero busco por codigo de Producto
            Dim Client As New clsCliente()
            If Cli_Id > 0 Then
                Client = ClienteDao.TraeCliente(Cli_Id)
            End If
            If Client.Cli_Id > 0 Then
                ListaClientes.Add(Client)
                Client.Dispose()
                Return ListaClientes
            End If
            Client.Dispose()

            ''Armo la consulta en un string segun los parametros Cargados
            Dim Cn As New SqlClient.SqlConnection
            Dim Cm As New SqlClient.SqlCommand
            Dim Dr As SqlClient.SqlDataReader
            Dim StringSelect As String
            Dim StrinWhere As String

            Try
                Cn.ConnectionString = My.Resources.Conexion
                Cn.Open()
                Cm.CommandType = CommandType.Text

                StrinWhere = "where Cod_Suc = " & Cod_Suc.ToString.Trim & " AND Tipo = '" & Tipo.Trim.ToUpper & "' "

                If Cli_Id > 0 Then
                    StrinWhere = StrinWhere & " AND cli_id LIKE '%" & Cli_Id.ToString.Trim & "%' "
                End If

                If DNI.Trim.Length > 0 Then
                    StrinWhere = StrinWhere & " AND dni LIKE '%" & DNI.ToString.Trim & "%' "
                End If

                If CUIT.Trim.Length > 0 Then
                    StrinWhere = StrinWhere & " AND cuit LIKE '%" & CUIT.ToString.Trim & "%' "
                End If

                If Nombre.Trim.Length > 0 Then
                    StrinWhere = StrinWhere & " AND nombre LIKE '%" & Nombre.ToString.Trim & "%' "
                End If

                If Apellido.Trim.Length > 0 Then
                    StrinWhere = StrinWhere & " AND apellido LIKE '%" & Apellido.ToString.Trim & "%' "
                End If

                If Direccion.Trim.Length > 0 Then
                    StrinWhere = StrinWhere & " AND direccion LIKE '%" & Direccion.ToString.Trim & "%' "
                End If

                If DirNro.Trim.Length > 0 Then
                    StrinWhere = StrinWhere & " AND altura LIKE '%" & DirNro.ToString.Trim & "%' "
                End If

                If DirDepto.Trim.Length > 0 Then
                    StrinWhere = StrinWhere & " AND depto LIKE '%" & DirDepto.ToString.Trim & "%' "
                End If

                If Telefono.Trim.Length > 0 Then
                    StrinWhere = StrinWhere & " AND tel LIKE '%" & Telefono.ToString.Trim & "%' "
                End If

                If Email.Trim.Length > 0 Then
                    StrinWhere = StrinWhere & " AND email LIKE '%" & Email.ToString.Trim & "%' "
                End If


                StringSelect = "select cli_id, nombre, apellido, direccion, altura, " & _
                               "depto, dni, tel, cuit, email, Tipo, Cod_Suc " & _
                               "from cliente " & StrinWhere

                Cm.CommandText = StringSelect

                Cm.Connection = Cn
                Dr = Cm.ExecuteReader
                If Dr.HasRows = True Then
                    While Dr.Read
                        Dim Cliente As New clsCliente()
                        Cliente.Cli_Id = Dr.GetInt32(0)
                        Cliente.Nombre = Dr.GetString(1).Trim.ToUpper
                        Cliente.Apellido = Dr.GetString(2).Trim.ToUpper

                        If Dr.IsDBNull(3) Then
                            Cliente.Direccion = "-"
                        Else
                            Cliente.Direccion = Dr.GetString(3).Trim.ToUpper
                        End If
                        If Dr.IsDBNull(4) Then
                            Cliente.Altura = "-"
                        Else
                            Cliente.Altura = Dr.GetString(4).Trim.ToUpper
                        End If
                        If Dr.IsDBNull(5) Then
                            Cliente.Depto = "-"
                        Else
                            Cliente.Depto = Dr.GetString(5).Trim.ToUpper
                        End If
                        If Dr.IsDBNull(6) Then
                            Cliente.Dni = "-"
                        Else
                            Cliente.Dni = Dr.GetString(6).Trim.ToUpper
                        End If
                        If Dr.IsDBNull(7) Then
                            Cliente.Tel = "-"
                        Else
                            Cliente.Tel = Dr.GetString(7).Trim.ToUpper
                        End If
                        If Dr.IsDBNull(8) Then
                            Cliente.Cuit = "-"
                        Else
                            Cliente.Cuit = Dr.GetString(8).Trim.ToUpper
                        End If
                        If Dr.IsDBNull(9) Then
                            Cliente.Email = "-"
                        Else
                            Cliente.Email = Dr.GetString(9).Trim.ToUpper
                        End If

                        Cliente.Tipo = Dr.GetString(10).Trim.ToUpper
                        Cliente.Cod_Suc = Dr.GetInt32(11)

                        ListaClientes.Add(Cliente)
                        Cliente.Dispose()
                    End While

                    Dr.Close()
                    Cn.Close()
                End If
                Return ListaClientes
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                Cn.Dispose()
                Cm.Dispose()
            End Try
        Catch ex As Exception
        Finally
            ClienteDao.Dispose()
        End Try

        Return ListaClientes
    End Function

    Public Sub New()
        MyBase.New()
    End Sub
    Public Sub Dispose()
        MyBase.Finalize()
    End Sub


End Class
